/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package amd.database;

import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Friendy Mardhika Parhan
 * edited by Latifah Dwi Nurachma
 */
public class ConnectDB {
    private Connection conn;
    private Statement stmt;
    private boolean valid;

    public ConnectDB(String URL, String user, String password) throws CannotConnectDBException {
        System.out.println("Masuk ConnectDB");
        System.out.println(URL);
        System.out.println(user);
        System.out.println(password);
        try {
            conn = null;
            stmt = null;
            //System.out.println(URL);
            Class.forName("com.mysql.jdbc.Driver"); // Load Driver
            conn = DriverManager.getConnection(URL, user, password); // Create statement
            stmt = conn.createStatement(); // Create statement
            valid = true;
            //createTable();
        } catch (Exception x) {
            System.out.println(x);
            throw new CannotConnectDBException(URL);
        }
        
        //createTable();
    }

    public ConnectDB() throws CannotConnectDBException{
        this("jdbc:mysql://localhost:3306/db_amd_lsa", "root", "");
    }

    public boolean isValidDatabase(){
        return valid;
    }

    public void closeConnection(){
        try {
            stmt.close ();
            conn.close ();
        } catch (SQLException sqlEx) {
            System.out.println ("Could not close connection:" +
            sqlEx.toString ());
        }
    }

    public boolean isExistInDatabase(String sql){
        try {
            getSingleData(sql, 1);
        } catch (QueryNoResultException ex) {
            return false;
        }
        return true;
    }

    public void createStatement(String sql){
        try {
            stmt.executeUpdate (sql); // insert the values
        } catch (SQLException sqlEx) {
            if(!sqlEx.toString().contains("duplicat"))
//               System.out.println("duplikasi dihindari");
//            else
               System.out.println("eksekusi gagal..."+ sqlEx.toString());
        }
    }

    public Object getSingleData(String sql, int colom) throws QueryNoResultException{
        Object data = null;
        try{
            data = null;
            ResultSet rs = stmt.executeQuery(sql);
            rs.next();
            data = rs.getObject(colom);
        }
        catch(Exception e){
//            System.out.println("Tidak ada record yg match dgn kondisi yang diinginkan");
//            System.out.println("\tsql="+sql);
//            System.out.println(e);
            throw new QueryNoResultException();

        }
        return data;
    }

    public ArrayList<ArrayList<Object>> getAllDatas(String sql) throws QueryNoResultException{
        ArrayList<ArrayList<Object>> datas = new ArrayList<ArrayList<Object>> ();
        try{
            System.out.println("SQL="+sql);
            ResultSet resSet = stmt.executeQuery(sql);
            ResultSetMetaData resMetData = resSet.getMetaData();
            int sumCol = resMetData.getColumnCount();
            System.out.println("...Jumlah kolom...="+sumCol);

            while(resSet.next()){
                ArrayList<Object> temp = new ArrayList<Object>();
                for(int i=1; i<=sumCol; i++) {
                    Object obj = resSet.getObject(resMetData.getColumnName(i));
                    System.out.println("...NamaKolom["+i+"]...="+resMetData.getColumnName(i)+obj.toString());
                    temp.add(obj);
                }
                datas.add(temp);
            }
        }
        catch(Exception e){
            System.out.println(e);
            throw new QueryNoResultException();
        }
        return datas;
    }

        public ArrayList<Object> getAllIdDatas(String sql) throws QueryNoResultException{
        ArrayList<Object> datas = new ArrayList<Object> ();
        try{
            ResultSet resSet = stmt.executeQuery(sql);
            ResultSetMetaData resMetData = resSet.getMetaData();
            while(resSet.next()){
                 Object obj = resSet.getObject(resMetData.getColumnName(1));
                datas.add(obj);
            }
        }
        catch(Exception e){
            System.out.println(e);
            throw new QueryNoResultException();
        }
        return datas;
    }

    public int countRows(String tableName) {
    // select the number of rows in the table
        ResultSet rs = null;
        int rowCount = -1;
        try {
          stmt = conn.createStatement();
          rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tableName);
          // get the number of rows from the result set
          rs.next();
          rowCount = rs.getInt(1);
        }
        catch(Exception e){
        }

        return rowCount;
    }
    public int countRowsCustomised(String sql) throws QueryNoResultException {
    // select the number of rows in the table
        ResultSet rs = null;
        int rowCount = -1;
        try {
          stmt = conn.createStatement();
          rs = stmt.executeQuery(sql);
          // get the number of rows from the result set
          rs.next();
          rowCount = rs.getInt(1);
        }
        catch(Exception e){
            throw new QueryNoResultException();
        }
        return rowCount;
    }
}
